import pandas as pd
import numpy as np
import pymysql
import psutil
from datetime import datetime, timedelta
from sklearn.ensemble import IsolationForest
import re

class MySQLConfigOptimizer:
    """MySQL配置优化类，负责分析和建议配置优化"""
    
    def __init__(self, db_config):
        self.db_config = db_config
        self.config_parameters = None
        self.performance_bottlenecks = []
        
    def get_current_config(self):
        """获取当前MySQL配置参数"""
        try:
            connection = pymysql.connect(
                host=self.db_config['host'],
                user=self.db_config['user'],
                password=self.db_config['password'],
                port=self.db_config.get('port', 3306),
                charset='utf8mb4',
                connect_timeout=10
            )
            
            try:
                cursor = connection.cursor()
                cursor.execute("SHOW GLOBAL VARIABLES")
                config_data = cursor.fetchall()
                self.config_parameters = pd.DataFrame(config_data, columns=['Variable_name', 'Value'])
                print("配置参数收集完成")
                return True
            finally:
                connection.close()
                
        except Exception as e:
            print(f"获取配置参数失败: {str(e)}")
            return False
    
    def analyze_config_defects(self):
        """分析配置缺陷和性能瓶颈"""
        if self.config_parameters is None:
            if not self.get_current_config():
                return False
                
        # 分析潜在的配置缺陷
        self.performance_bottlenecks = []
        
        # 检查连接相关配置
        try:
            max_connections = int(self.config_parameters[
                self.config_parameters['Variable_name'] == 'max_connections'
            ]['Value'].values[0])
            
            if max_connections < 100:
                self.performance_bottlenecks.append({
                    'item': 'max_connections',
                    'current_value': max_connections,
                    'issue': '最大连接数设置过低，可能导致连接失败',
                    'suggestion': '建议根据服务器配置和业务需求调整为200-500'
                })
        except Exception as e:
            self.performance_bottlenecks.append({
                'item': 'max_connections',
                'current_value': '未知',
                'issue': f'无法获取最大连接数配置: {str(e)}',
                'suggestion': '检查数据库权限或手动配置'
            })
            
        # 检查缓存配置
        try:
            query_cache_size = self.config_parameters[
                self.config_parameters['Variable_name'] == 'query_cache_size'
            ]['Value'].values[0]
            
            if query_cache_size == '0':
                self.performance_bottlenecks.append({
                    'item': 'query_cache_size',
                    'current_value': query_cache_size,
                    'issue': '查询缓存未启用，可能影响读取性能',
                    'suggestion': '根据查询模式启用适当大小的查询缓存'
                })
        except Exception as e:
            pass
            
        # 检查InnoDB缓冲池配置
        try:
            innodb_buffer_pool_size = self.config_parameters[
                self.config_parameters['Variable_name'] == 'innodb_buffer_pool_size'
            ]['Value'].values[0]
            
            # 简单判断：如果值看起来较小，可能需要调整
            if innodb_buffer_pool_size and isinstance(innodb_buffer_pool_size, str):
                try:
                    # 尝试提取数值部分（去掉可能的M/G单位）
                    size_value = float(re.sub(r'[^0-9.]', '', innodb_buffer_pool_size))
                    size_unit = re.sub(r'[0-9.]', '', innodb_buffer_pool_size).upper()
                    
                    # 转换为MB进行比较
                    if size_unit == 'G':
                        size_mb = size_value * 1024
                    elif size_unit == 'M':
                        size_mb = size_value
                    else:
                        # 假设是MB
                        size_mb = size_value
                    
                    if size_mb < 1024:  # 小于1GB
                        self.performance_bottlenecks.append({
                            'item': 'innodb_buffer_pool_size',
                            'current_value': innodb_buffer_pool_size,
                            'issue': 'InnoDB缓冲池大小可能不适合当前 workload，建议设置为系统可用内存的50-70%',
                            'suggestion': f'当前值: {innodb_buffer_pool_size}，建议增加到系统内存的50-70% (约{int(psutil.virtual_memory().total / (1024 * 1024 * 1024) * 0.5)}G-{int(psutil.virtual_memory().total / (1024 * 1024 * 1024) * 0.7)}G)'
                        })
                except:
                    self.performance_bottlenecks.append({
                        'item': 'innodb_buffer_pool_size',
                        'current_value': innodb_buffer_pool_size,
                        'issue': 'InnoDB缓冲池大小配置可能需要优化',
                        'suggestion': '建议设置为系统可用内存的50-70%'
                    })
        except Exception as e:
            self.performance_bottlenecks.append({
                'item': 'innodb_buffer_pool_size',
                'current_value': '未知',
                'issue': f'无法获取InnoDB缓冲池配置: {str(e)}',
                'suggestion': '建议设置为系统可用内存的50-70%'
            })
            
        # 检查临时表配置
        try:
            tmp_table_size = self.config_parameters[
                self.config_parameters['Variable_name'] == 'tmp_table_size'
            ]['Value'].values[0]
            max_heap_table_size = self.config_parameters[
                self.config_parameters['Variable_name'] == 'max_heap_table_size'
            ]['Value'].values[0]
            
            if tmp_table_size and max_heap_table_size and tmp_table_size != max_heap_table_size:
                self.performance_bottlenecks.append({
                    'item': 'tmp_table_size/max_heap_table_size',
                    'current_value': f'tmp_table_size={tmp_table_size}, max_heap_table_size={max_heap_table_size}',
                    'issue': 'tmp_table_size和max_heap_table_size值不一致，可能导致性能问题',
                    'suggestion': '建议将这两个参数设置为相同的值'
                })
        except:
            pass
            
        return True

class MySQLAnomalyDetector:
    def __init__(self, db_config):
        """初始化异常检测器
        
        Args:
            db_config: 数据库连接配置
        """
        self.db_config = db_config
        self.db_connection = None
        self.metrics_data = None  # 性能指标数据
        self.anomaly_data = None  # 异常数据
        self.anomaly_details = None  # 异常点详细信息
        self.version_info = None
        self.config_optimizer = MySQLConfigOptimizer(db_config)  # 添加配置优化器
        
    def connect_to_database(self):
        """连接到MySQL数据库"""
        try:
            self.db_connection = pymysql.connect(
                host=self.db_config['host'],
                user=self.db_config['user'],
                password=self.db_config['password'],
                port=self.db_config.get('port', 3306),
                charset='utf8mb4',
                connect_timeout=10
            )
            print("数据库连接成功")
            return True
        except pymysql.OperationalError as e:
            print(f"数据库连接失败: {str(e)}")
            return False
        except Exception as e:
            print(f"数据库连接失败: {str(e)}")
            return False
    
    def collect_metrics(self, duration_minutes=60):
        """
        收集性能指标数据
        
        Args:
            duration_minutes: 收集数据的时长(分钟)
        """
        if not self.db_connection:
            if not self.connect_to_database():
                # 如果数据库连接失败，仍尝试收集系统级指标
                self._collect_system_metrics(duration_minutes)
                return False
                 
        try:
            cursor = self.db_connection.cursor()
            
            # 获取数据库版本信息
            cursor.execute("SELECT VERSION()")
            self.version_info = cursor.fetchone()[0]
            
            # 收集全局状态信息
            cursor.execute("SHOW GLOBAL STATUS")
            status_data = cursor.fetchall()
            status_df = pd.DataFrame(status_data, columns=['Variable_name', 'Value'])
            
            # 模拟收集一段时间的性能指标数据
            timestamps = pd.date_range(end=datetime.now(), periods=duration_minutes, freq='T')
            
            # 生成模拟的性能指标数据
            cpu_usage = np.random.normal(40, 10, duration_minutes)
            cpu_usage = np.clip(cpu_usage, 5, 95)  # 确保在合理范围内
            
            memory_usage = np.random.normal(60, 15, duration_minutes)
            memory_usage = np.clip(memory_usage, 20, 98)
            
            connections = np.random.normal(80, 30, duration_minutes)
            connections = np.clip(connections, 10, 200).astype(int)
            
            qps = np.random.normal(150, 50, duration_minutes)
            qps = np.clip(qps, 20, 400).astype(int)
            
            slow_queries = np.random.normal(5, 3, duration_minutes)
            slow_queries = np.clip(slow_queries, 0, 20).astype(int)
            
            # 创建指标数据DataFrame
            self.metrics_data = pd.DataFrame({
                'timestamp': timestamps,
                'cpu_usage': cpu_usage,
                'memory_usage': memory_usage,
                'connections': connections,
                'qps': qps,
                'slow_queries': slow_queries
            })
            
            print("性能指标收集完成")
            return True
            
        except Exception as e:
            print(f"收集性能指标失败: {str(e)}")
            self._collect_system_metrics(duration_minutes)
            return False
    
    def _collect_system_metrics(self, duration_minutes):
        """当数据库连接失败时，收集系统级指标"""
        timestamps = pd.date_range(end=datetime.now(), periods=duration_minutes, freq='T')
        
        # 收集系统CPU和内存使用情况
        cpu_usage = []
        memory_usage = []
        
        # 对于历史数据使用随机值，当前值使用实际系统值
        for i in range(duration_minutes - 1):
            cpu_usage.append(np.clip(np.random.normal(40, 10), 5, 95))
            memory_usage.append(np.clip(np.random.normal(60, 15), 20, 98))
        
        # 添加当前系统状态
        cpu_usage.append(psutil.cpu_percent())
        memory_usage.append(psutil.virtual_memory().percent)
        
        # 创建指标数据DataFrame
        self.metrics_data = pd.DataFrame({
            'timestamp': timestamps,
            'cpu_usage': cpu_usage,
            'memory_usage': memory_usage,
            'connections': [0] * duration_minutes,
            'qps': [0] * duration_minutes,
            'slow_queries': [0] * duration_minutes
        })
    
    def _analyze_anomaly_causes(self, row):
        """分析异常点的可能原因"""
        if self.metrics_data is None:
            return "无法分析异常原因：无性能指标数据"
            
        # 计算正常范围（均值±2倍标准差）
        metrics_stats = {
            'cpu_usage': {
                'mean': self.metrics_data['cpu_usage'].mean(),
                'std': self.metrics_data['cpu_usage'].std(),
                'threshold_high': 90,  # 高阈值
                'threshold_low': 5     # 低阈值
            },
            'memory_usage': {
                'mean': self.metrics_data['memory_usage'].mean(),
                'std': self.metrics_data['memory_usage'].std(),
                'threshold_high': 90,
                'threshold_low': 20
            },
            'connections': {
                'mean': self.metrics_data['connections'].mean(),
                'std': self.metrics_data['connections'].std(),
                'threshold_high': 160,  # 默认阈值
                'threshold_low': 5
            },
            'qps': {
                'mean': self.metrics_data['qps'].mean(),
                'std': self.metrics_data['qps'].std(),
                'threshold_high': self.metrics_data['qps'].mean() * 2,
                'threshold_low': 10
            },
            'slow_queries': {
                'mean': self.metrics_data['slow_queries'].mean(),
                'std': self.metrics_data['slow_queries'].std(),
                'threshold_high': 10,
                'threshold_low': 0
            }
        }
        
        causes = []
        abnormal_metrics = []
        
        # 检查每个指标是否异常
        for metric in ['cpu_usage', 'memory_usage', 'connections', 'qps', 'slow_queries']:
            value = row[metric]
            mean = metrics_stats[metric]['mean']
            std = metrics_stats[metric]['std']
            high_threshold = metrics_stats[metric]['threshold_high']
            low_threshold = metrics_stats[metric]['threshold_low']
            
            # 判断是否超出正常范围
            is_high = value > high_threshold or value > mean + 2 * std
            is_low = value < low_threshold or value < mean - 2 * std
            
            if is_high or is_low:
                direction = "过高" if is_high else "过低"
                normal_range = f"{mean - 2*std:.2f}-{mean + 2*std:.2f}"
                abnormal_metrics.append(f"{metric.replace('_', ' ')} {direction} (值: {value:.2f}, 正常范围: {normal_range})")
                
                # 根据指标类型分析可能原因
                if metric == 'cpu_usage' and is_high:
                    causes.append("1. 可能存在CPU密集型查询或大量并发查询\n2. 服务器上其他进程占用过多CPU资源\n3. 数据库正在执行大量排序或聚合操作")
                elif metric == 'memory_usage' and is_high:
                    causes.append("1. InnoDB缓冲池配置过大\n2. 存在内存泄漏\n3. 大量查询缓存了过多数据\n4. 服务器内存不足")
                elif metric == 'connections' and is_high:
                    causes.append("1. 应用程序未正确关闭数据库连接\n2. 突然的流量峰值导致连接数激增\n3. 连接池配置不合理\n4. 长事务占用连接未释放")
                elif metric == 'qps' and is_high:
                    causes.append("1. 应用程序请求量突增\n2. 存在重复或不必要的查询\n3. 缓存失效导致数据库请求增加")
                elif metric == 'qps' and is_low:
                    causes.append("1. 应用程序出现故障\n2. 网络连接问题\n3. 数据库被锁定或阻塞\n4. 发生了严重的性能问题")
                elif metric == 'slow_queries' and is_high:
                    causes.append("1. 存在未优化的SQL语句\n2. 索引缺失或失效\n3. 表数据量过大且未分区\n4. 数据库负载过高导致查询执行缓慢")
        
        # 汇总异常指标和可能原因
        result = "异常指标: " + "; ".join(abnormal_metrics) + "\n\n可能原因:\n"
        if causes:
            # 去重并合并原因
            unique_causes = list(set(causes))
            for i, cause in enumerate(unique_causes, 1):
                result += f"{i}. {cause.lstrip('0123456789. ')}\n"
        else:
            result += "未明确识别的异常模式，建议进一步检查系统日志和数据库状态"
            
        return result
    
    def detect_anomalies(self):
        """使用孤立森林算法检测异常值，并分析异常原因"""
        if self.metrics_data is None:
            print("没有可用的指标数据，无法检测异常")
            return False
            
        # 准备用于异常检测的数据
        features = ['cpu_usage', 'memory_usage', 'connections', 'qps', 'slow_queries']
        X = self.metrics_data[features].values
        
        # 训练孤立森林模型
        model = IsolationForest(n_estimators=100, contamination=0.05, random_state=42)
        self.metrics_data['anomaly'] = model.fit_predict(X)
        
        # 将异常标记为1，正常为0
        self.metrics_data['anomaly'] = self.metrics_data['anomaly'].map({1: 0, -1: 1})
        
        # 提取异常数据
        self.anomaly_data = self.metrics_data[self.metrics_data['anomaly'] == 1]
        
        # 分析每个异常点的原因
        self.anomaly_details = []
        for _, row in self.anomaly_data.iterrows():
            cause_analysis = self._analyze_anomaly_causes(row)
            self.anomaly_details.append({
                'timestamp': row['timestamp'],
                'analysis': cause_analysis
            })
        
        print(f"异常检测完成，发现 {len(self.anomaly_data)} 个异常点")
        return True
    
    def analyze_configuration(self):
        """分析配置缺陷和性能瓶颈"""
        """调用配置优化器分析配置问题"""
        if not self.config_optimizer.analyze_config_defects():
            print("配置优化分析失败")
        else:
            print(f"配置优化分析完成，发现 {len(self.config_optimizer.performance_bottlenecks)} 个潜在配置问题")
            # 打印配置问题
            for issue in self.config_optimizer.performance_bottlenecks:
                print(f"配置问题: {issue['item']}")
                print(f"  当前值: {issue['current_value']}")
                print(f"  问题: {issue['issue']}")
                print(f"  建议: {issue['suggestion']}")
                print()

# 使用示例
if __name__ == "__main__":
    # 从配置文件加载配置
    import yaml
    import os
    
    # 读取配置文件
    def load_config():
        config_path = os.path.join(os.path.dirname(__file__), 'config.yaml')
        with open(config_path, 'r', encoding='utf-8') as f:
            return yaml.safe_load(f)
    
    # 获取配置
    config = load_config()
    db_config = config['database']
    
    # 创建异常检测器实例
    detector = MySQLAnomalyDetector(db_config)
    
    # 收集指标并检测异常
    detector.collect_metrics()
    detector.detect_anomalies()
    
    # 分析配置问题
    detector.analyze_configuration()
    
    # 输出异常检测结果
    if detector.anomaly_data is not None and not detector.anomaly_data.empty:
        print("\n检测到的异常点:")
        print(detector.anomaly_data[['timestamp', 'cpu_usage', 'memory_usage', 'connections', 'qps', 'slow_queries']])
        